﻿SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

create PROCEDURE [dbo].[sp_GetHTTPRequest2]
@id bigint OUTPUT,
@sms_id bigint output,
@DateTime varchar(24) OUTPUT,
@Protocol varchar(5) OUTPUT,
@Method varchar(4) OUTPUT,
@ContentType varchar(36) OUTPUT,
@Login varchar(24) OUTPUT,
@Password varchar(32) OUTPUT,
@Host varchar(48) OUTPUT,
@Port varchar(5) OUTPUT,
@Script varchar(64) OUTPUT,
@Query varchar(1024) OUTPUT,
@PostData varchar(1024) OUTPUT,
@Service varchar(12) OUTPUT,
@Misc varchar (1024) OUTPUT,
@CertFile varchar (50) OUTPUT,
@KeyFile varchar (50) OUTPUT,
@RootCertFile varchar (50) OUTPUT

with encryption
 AS
declare 
@K int,
@R int,
@SMSAnswerN varchar(1024),
@T2 varchar(8),
@Result dtResult,
@Answer_id bigint,
@T varchar(160),
@Res2 smallint, 
@Order varchar(50),
@TagPhone dtPhoneNumber

BEGIN TRY
exec ind_setactivity 'Requestor'
--exec ind_setactivity 'Requestor2'

	--set @T=isnull(@service,'')+' '+CAST(isnull(@id,0) as varchar)+' '+convert(varchar,@Datetime,104)
	--if @T is not null exec sms_p.dbo.prints_t 'GHR 033','http 0:', @T,32


select top 1 @id=[id], @sms_id=sms_id, @DateTime =[DateTime], @Protocol=Protocol, @Method=Method, @ContentType=ContentType, 
	@Login=Login, @Password=[Password], @Host=Host, @Port=Port, @Script=Script, @Query=Query, 
	@PostData=PostData, @Service=Service, @Misc=Misc, @CertFile=CertFile, @KeyFile=KeyFile, @RootCertFile=RootCertFile
from Services.dbo.HTTPRequests where ResponseTime is NULL and Status='New' 
and Service<>'PE_BEE'    -- Билайн обслуживает другой шлюз !!!
and Service<>'PA_OTMTS'   --- MTS обслуживает другой шлюз !!!
----- !!!!
----- 
and Service<>'PA_OTP2'
and Service<>'PA_OTWC'
and Service<>'PA_OTRBKM'
and Service<>'PE_YOTA'
and Service<>'PA_OTLA1'
and Service<>'PA_OTLA2'
and Service<>'PA_OTLA3'
and Service<>'PE_VANET'
and Service<>'PA_OTSI'
and Service<>'PA_OTDI'
and Service<>'PE_KATRINA'
and Service<>'PA_OTKTV2'
and Service<>'PA_OTAVIAN'
and UPPER(Service)<>'PE_TELIX'
order by [id] asc 

Select @R=@@Rowcount
if @R>0
	begin
	set @T=isnull(@service,'')+' '+CAST(isnull(@id,0) as varchar)+' '+convert(varchar,@Datetime,104)
	--exec sms_p.dbo.prints_t 'GHR 034','http 1:', @T,32

	goto nxt
	end
---if @@Rowcount=0
else
	select top 1 @id=[id], @sms_id=sms_id, @DateTime =[DateTime], @Protocol=Protocol, @Method=Method, @ContentType=ContentType, 
		@Login=Login, @Password=[Password], @Host=Host, @Port=Port, @Script=Script, @Query=Query, 
		@PostData=PostData, @Service=Service, @Misc=Misc, @CertFile=CertFile, @KeyFile=KeyFile, @RootCertFile=RootCertFile
	from Services.dbo.HTTPRequests where ResponseTime is NULL and Status='New'
and Service<>'PE_BEE'    -- Билайн обслуживает другой шлюз !!!
and Service<>'PA_OTMTS'   --- MTS обслуживает другой шлюз !!!
---- !!!!
--and Service<>'PA_OTLA1'
--and Service<>'PA_OTLA2'
--and Service<>'PA_OTLA3'
and Service<>'PE_VANET'
---and Service<>'PE_YOTA'
and Upper(Service)<>'TEST'
--and Service<>'PA_OTAVIAN'
--and Service<>'PE_VANET'
and Service<>'PA_OTDI'
and UPPER(Service)<>'PE_TELIX'
and Service<>'PA_OTKTV2'

---and Service<>'PA_OTMO'
--and Service<>'PA_OTRBKM'
--and Service<>'PA_OTLR'
--and Service<>'PA_OTSI'
--and Service<>'PE_KATRINA'
--and Service<>'PA_OTAVIAN'

	order by [id] asc 

Select @R=@@Rowcount
if @R>0
	begin
	set @T=isnull(@service,'')+' '+CAST(isnull(@id,0) as varchar)+' '+convert(varchar,@Datetime,104)
	exec sms_p.dbo.prints_t 'GHR 035','http 2:', @T,32

	goto nxt
	end
else
	select top 1 @id=[id], @sms_id=sms_id, @DateTime =[DateTime], @Protocol=Protocol, @Method=Method, @ContentType=ContentType, 
		@Login=Login, @Password=[Password], @Host=Host, @Port=Port, @Script=Script, @Query=Query, 
		@PostData=PostData, @Service=Service, @Misc=Misc, @CertFile=CertFile, @KeyFile=KeyFile, @RootCertFile=RootCertFile
	from Services.dbo.HTTPRequests where ResponseTime is NULL and Status='New'
and Service<>'PE_BEE'    -- Билайн обслуживает другой шлюз !!!
and Service<>'PA_OTMTS'   --- MTS обслуживает другой шлюз !!!
---- !!!!
--and Service<>'PA_OTMO'
--and UPPER(Service)<>'TELIX'
	order by [id] asc 

set @T=isnull(@service,'')+' '+CAST(isnull(@id,0) as varchar)+' '+convert(varchar,@Datetime,104)
if @T is not null 
	exec sms_p.dbo.prints_t 'GHR 036','http 3:', @T,32

nxt:

if @id is NULL
	begin
		set @Method=''
	set @Login=''
	set @Password=''
	set @Host =''
	set @Port =''
	set @Script =''
	set @Query =''
	set @PostData=''
	set @id=0
	set @sms_id=0
	set @Service=''
	set @ContentType=''
	set @Misc=''
	set @CertFile=''
	set @KeyFile=''
	set @RootCertFile=''
	return
	end


select @K=Count([id]) from Services.dbo.HTTPResponse where Request_id=@id 

if @K>=5
	begin
	exec sms_p.dbo.prints 'GHR 017','GetHTTPRequest: exists but failed:', @id,32
	exec sms_p.dbo.prints 'GHR 034','Misc:', @Misc,32
	update Services.dbo.HTTPRequests set Status='Fail', ResponseTime=getdate() where [id]=@id
	if Upper(@Service)='LANCKT'
		begin
		exec sp_StrParse @Misc, 1 , '~', ' ' , @TagPhone OUTPUT , @Res2 OUTPUT 
		set @SmsAnswerN='Нет связи с LANCK-Telecom.  Платеж ' +CAST(@id as varchar)+' / +'+@TagPhone+' будет доставлен позже.  Справки на  http://www.sms4pay.net'  
		end
	else if Upper(@Service)='LANCKAB'
		begin
		exec sp_StrParse @Misc, 1 , '~', ' ' , @TagPhone OUTPUT , @Res2 OUTPUT 
		set @SmsAnswerN='Платеж ' +CAST(@id as varchar)+' / +'+@TagPhone+' LANCK-Telecom будет доставлен.  Справки на  http://www.sms4pay.net'  
		end
	else if Upper(@Service)='ENTREATY'
		begin
		exec sp_StrParse @Misc, 1 , '~', ' ' , @TagPhone OUTPUT , @Res2 OUTPUT 
		declare @s1 varchar(9), @s2 varchar(9)
		set @s2=CAST(@id as varchar)
		if len(@s2)=3 
			set @S1='000'
		else if len(@s2)=4
			set @S1='00'
		else if len(@s2)=5
			set @S1='0'
		else 
			set  @s1=''
		set @SmsAnswerN='Послание  будет доставлено.  Учетный номер: '+'119'+@S1+@s2+'. Справки на  http://www.send2sky.net'  
		end
	else if Upper(@Service)='RMB'
		begin
		exec sp_StrParse @Misc, 1 , '*', ' ' , @Order OUTPUT , @Res2 OUTPUT
		exec sp_StrParse @Misc, @Res2 , '*', ' ' , @T2  OUTPUT , @Res2 OUTPUT
		exec sp_StrParse @Misc, @Res2 , '*', ' ' , @TagPhone  OUTPUT , @Res2 OUTPUT

		if @Order is NULL
			set @Order=''
		exec prints 'GHR 040','GEZ number:',@Order,32
		exec prints 'GHR 041','parol:',@T2,32
		set @SmsAnswerN='mobIGamer GEZ'+@order+' Zaregistriruysya zavtra na www.mobigamer.ru Parol:'+@t2    
		end
	else
		set @SmsAnswerN='Нет связи с поставщиком услуги. Платеж ' +CAST(@id as varchar)+' будет доставлен позже.  Справки на  http://www.sms4pay.net'  


	if Upper(@Service)  not in ( 'UA', 'WMoney') and (@TagPhone is not NULL)
		begin
		exec sp_InsToAnswers  @SMS_id, @TagPhone, @SmsAnswerN, 1, 		'PAY', 0, @Result OUTPUT, @Answer_id OUTPUT

	exec prints 'GHR 114','@Result:',@Result,32
	exec prints 'GHR 115','@Answer_id:',@Answer_id,32
	if @Answer_id<>NULL
			begin
			exec prints 'GHR 116','Answer:', @Answer_id ,32
			end
	else
			begin
			set @T='GetHTTPRequest/ins_toAnswer ERROR, sms_id:'+Cast(@sms_id as varchar)+' Id:'+Cast(@Id as varchar)+' Service:'+@Service  
			exec sp_SetNotification 'RollBack', @T
			end
		end

	set @DateTime=''
	set @Protocol =''
	set @Method=''
	set @Login=''
	set @Password=''
	set @Host =''
	set @Port =''
	set @Script =''
	set @Query =''
	set @PostData=''
	set @id=0
	set @sms_id=0
	set @Service=''
	set @ContentType=''
	set @Misc=''
	set @CertFile=''
	set @KeyFile=''
	set @RootCertFile=''
	return
	end



if @DateTime is NULL
	set @DateTime=''

if @Protocol  is NULL
	set @Protocol =''
if @Method  is NULL
	set @Method =''
if @ContentType  is NULL
	set @ContentType =''
if @Login is NULL
	set @Login=''
if @Password is NULL
	set @Password=''
if @Host is NULL
	set @Host =''
if @Port is NULL
	set @Port =''
if @Script is NULL
	set @Script =''

if  Upper(@Service) in ('PES', 'PESR')
	set @Query=''
else if  Upper(@Service) in ('UA')
	begin
	if @Query is NULL or @Query='' 
		set @Query ='cmp_id='+CAST(@id as varchar)
	else
		set @Query ='cmp_id='+CAST(@id as varchar)+'&'+@Query
	end
else
	begin
	if @Query is NULL or @Query='' 
		set @Query ='Message_id='+CAST(@id as varchar)
	else
		set @Query ='Message_id='+CAST(@id as varchar)+'&'+@Query
	end


if @PostData is NULL
	set @PostData =''
if @sms_id is NULL
	set @sms_id=0
if  @Service is NULL
	set @Service=''
if  @Misc is NULL
	set @Misc=''
if  @CertFile is NULL
	set @CertFile=''
if  @RootCertFile is NULL
	set @RootCertFile=''
if  @KeyFile is NULL
	set @KeyFile=''


if @id is NULL
	set @id=0
else
	exec sms_p.dbo.prints 'GHR 015','GetHTTPRequest. It exist:', @id,32

	END TRY
	BEGIN CATCH

DECLARE @sMessage nvarchar(300);
		IF ERROR_NUMBER() IS NOT NULL
			SET @T  =isnull(@T,'')+' ERROR:'+CAST( ERROR_NUMBER() as varchar)

		exec meps.Control.usp_LogError @sMessage OUT;	-- диагностика ошибки
		set @T =@T+' '+ @sMessage
	set @DateTime=''
	set @Protocol =''
	set @Method=''
	set @Login=''
	set @Password=''
	set @Host =''
	set @Port =''
	set @Script =''
	set @Query =''
	set @PostData=''
	set @id=0
	set @sms_id=0
	set @Service=''
	set @ContentType=''
	set @Misc=''
	set @CertFile=''
	set @KeyFile=''
	set @RootCertFile=''
	exec sms_p.dbo.prints_t 'GHR catch','ERR:', @T,32
	END CATCH
GO
GRANT EXECUTE ON [dbo].[sp_GetHTTPRequest2] TO [Executer]